// backend/controllers/statsController.js

const db = require('../config/db');
const moment = require('moment');

exports.getDashboardOverview = async (req, res) => {
    try {
        const [totalRoomsResult] = await db.query('SELECT COUNT(*) AS total_rooms FROM Rooms');
        const totalRooms = totalRoomsResult[0].total_rooms;

        const [availableRoomsResult] = await db.query('SELECT COUNT(*) AS available_rooms FROM Rooms WHERE status = "空闲"');
        const availableRooms = availableRoomsResult[0].available_rooms;

        const [occupiedRoomsResult] = await db.query('SELECT COUNT(*) AS occupied_rooms FROM Rooms WHERE status = "已入住"');
        const occupiedRooms = occupiedRoomsResult[0].occupied_rooms;

        const today = moment().format('YYYY-MM-DD');
        const [todayRevenueResult] = await db.query(
            `SELECT COALESCE(SUM(total_amount), 0) AS today_revenue
             FROM Checkins
             WHERE DATE(checkout_date) = ? AND status = '已退房'`,
            [today]
        );
        const todayRevenue = parseFloat(todayRevenueResult[0].today_revenue || 0).toFixed(2);

        res.status(200).json({
            totalRooms,
            availableRooms,
            occupiedRooms,
            todayRevenue
        });

    } catch (err) {
        console.error('获取仪表盘概览数据失败:', err);
        res.status(500).json({ error: '获取仪表盘概览数据失败', details: err.message });
    }
};

exports.getRoomStats = async (req, res) => {
    try {
        const { startDate, endDate, room_type } = req.query;
        let query = `
            SELECT
                r.room_type,
                COUNT(c.checkin_id) AS total_checkins,
                SUM(CASE WHEN c.status = '已退房' THEN COALESCE(c.total_amount, 0) ELSE 0 END) AS total_revenue_for_type,
                AVG(CASE
                        WHEN c.status = '已退房' AND c.checkin_date IS NOT NULL AND c.checkout_date IS NOT NULL THEN
                            DATEDIFF(c.checkout_date, c.checkin_date) + 1
                        ELSE NULL
                    END) AS avg_stay_days
            FROM Rooms r
                     JOIN Checkins c ON r.room_id = c.room_id
            WHERE 1=1
        `;
        const params = [];

        if (startDate) {
            query += ' AND c.checkin_date >= ?';
            params.push(startDate);
        }
        if (endDate) {
            query += ' AND c.checkout_date <= ?';
            params.push(endDate);
        }
        if (room_type) {
            query += ' AND r.room_type = ?';
            params.push(room_type);
        }

        query += `
            GROUP BY r.room_type
            ORDER BY r.room_type
        `;

        const [rows] = await db.query(query, params);

        const processedRows = rows.map(row => ({
            ...row,
            total_revenue: parseFloat(row.total_revenue_for_type || 0).toFixed(2),
            avg_stay_days: row.avg_stay_days !== null ? parseFloat(row.avg_stay_days).toFixed(2) : 'N/A'
        }));

        res.status(200).json(processedRows);

    } catch (err) {
        console.error('获取房间销售统计错误:', err);
        res.status(500).json({ error: '获取房间销售统计失败', details: err.message });
    }
};

exports.getConsumptionStats = async (req, res) => {
    try {
        const { startDate, endDate, item_name } = req.query;
        let query = `
            SELECT
                con.item_name,
                COALESCE(SUM(con.quantity), 0) AS total_quantity,
                COALESCE(SUM(con.amount), 0) AS total_amount
            FROM Consumptions con
            WHERE 1=1
        `;
        const params = [];

        if (startDate) {
            query += ' AND con.consume_time >= ?';
            params.push(startDate);
        }
        if (endDate) {
            query += ' AND con.consume_time <= ?';
            params.push(endDate);
        }
        if (item_name) {
            query += ' AND con.item_name LIKE ?';
            params.push(`%${item_name}%`);
        }

        query += `
            GROUP BY con.item_name
            ORDER BY total_amount DESC
        `;

        const [rows] = await db.query(query, params);
        res.status(200).json(rows);
    } catch (err) {
        console.error('获取消费统计错误:', err);
        res.status(500).json({ error: '获取消费统计失败', details: err.message });
    }
};
